-- EXEC SP_WAFER_PKG_ASSY_STANDARD_COST_REPORT 2011, 2
 
 ALTER PROC SP_WAFER_PKG_ASSY_STANDARD_COST_REPORT ( @YEAR INT, @QUARTER INT)
 AS
 BEGIN
 	DECLARE @FAB_CURSOR CURSOR 
 	DECLARE @ASSM_ProcessStep_ID INT,@DB_ProcessStep_ID INT,@BOM_PROCESSSTEP_ID INT
 	DECLARE @PkgCost NUMERIC(18,4)
	DECLARE @YieldAssy NUMERIC(18,4)
 	DECLARE @CMOS_Cumm_Cost_Per_Die	NUMERIC(18,4)
 	DECLARE @MEMS_Cumm_Cost_Per_Die NUMERIC(18,4)
	DECLARE @Assembly_Out_Cost NUMERIC(18,4)
	DECLARE @Package VARCHAR(50)

 	CREATE TABLE #COST_TABLE ( [Part Number] VARCHAR(50), [CMOS Die Cost] NUMERIC(18,4), [MEMS Die Cost] NUMERIC(18,4),
    [ASSEMBLY Step COST] NUMERIC(18,4), [Assembly Yield] NUMERIC(18, 2), [Assembly Out Cost] NUMERIC (18, 4),Package VARCHAR(50),
    ProcessStep_Id INT   )
 
 	INSERT INTO #COST_TABLE( [Part Number], ProcessStep_Id ) 
 	SELECT P.Part_Number, P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type = 'Assembly' 
	
 	SET @FAB_CURSOR = CURSOR FOR SELECT ProcessStep_Id FROM #COST_TABLE
 	OPEN @FAB_CURSOR
 	FETCH NEXT FROM @FAB_CURSOR INTO @ASSM_ProcessStep_ID
 	WHILE @@FETCH_STATUS = 0
 	BEGIN
 		SELECT @DB_ProcessStep_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @ASSM_ProcessStep_ID, 0 ) WHERE PS_TYPE = 'Die Bank'
		SELECT @CMOS_Cumm_Cost_Per_Die = ISNULL( Cumm_Cost_Per_Die, 0.0) FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @DB_ProcessStep_ID AND Quarter = @QUARTER AND Year = @YEAR
 
		SELECT @Package = Parameter_Value FROM Process_Parameter WHERE ProcessStep_Id = @ASSM_ProcessStep_ID AND Parameter_Key = 'Package ID'
 
		SELECT @BOM_PROCESSSTEP_ID = bom_processstep_id FROM Assembly_BOM_Setup_Table WHERE assembly_processstep_id = @ASSM_ProcessStep_ID
		SELECT @MEMS_Cumm_Cost_Per_Die = ISNULL( Cumm_Cost_Per_Die, 0.0) FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @BOM_PROCESSSTEP_ID AND Quarter = @QUARTER AND Year = @YEAR
 
		SELECT @PkgCost = [Pkg Cost], @YieldAssy = [Yield_Assy]
			FROM [Wafer_Pkg_Cost_Table] WHERE ProcessStep_ID = @ASSM_ProcessStep_ID AND Quarter = @Quarter AND Year = @Year

		IF @YieldAssy = 0.0
		BEGIN
			SET @YieldAssy = 100.00
		END

		SET @Assembly_Out_Cost = ( ( ISNULL( @CMOS_Cumm_Cost_Per_Die, 0.0) + ISNULL( @MEMS_Cumm_Cost_Per_Die, 0.0) )/(ISNULL(@YieldAssy, 100.00)/100) ) + ISNULL(@PkgCost, 0.0)		
		
		UPDATE #COST_TABLE SET [CMOS Die Cost] = @CMOS_Cumm_Cost_Per_Die, [MEMS Die Cost] = @MEMS_Cumm_Cost_Per_Die,
		[ASSEMBLY Step COST] = @PkgCost, [Assembly Yield] = @YieldAssy, Package = @Package,
		[Assembly Out Cost] = @Assembly_Out_Cost WHERE ProcessStep_Id = @ASSM_ProcessStep_ID

		SET @CMOS_Cumm_Cost_Per_Die	= 0.0
		SET @MEMS_Cumm_Cost_Per_Die		= 0.0
		SET @ASSM_ProcessStep_ID = 0
		SET @PkgCost = 0.0
		SET @YieldAssy = 0.0
		SET @Assembly_Out_Cost = 0.0
		SET @Package = NULL
 		FETCH NEXT FROM @FAB_CURSOR INTO @ASSM_ProcessStep_ID
 	END 
 	CLOSE @FAB_CURSOR
 	DEALLOCATE @FAB_CURSOR
 
 	SELECT [Part Number], [CMOS Die Cost], [MEMS Die Cost],
    [ASSEMBLY Step COST] , ([Assembly Yield]/100.00) as [Assembly Yield], [Assembly Out Cost],Package FROM #COST_TABLE ORDER BY ProcessStep_Id,Package
 
 END

